package com.team16.budgetme;

import java.math.BigDecimal;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Creates an SQLite database to store Transactions and Accounts
 */
public class DataHandler {
    private SQLiteDatabase db;
    private DataHelper helper;
    private String[] accountColumns = { DataHelper.COLUMN_ID, DataHelper.COLUMN_ACCOUNT,
            DataHelper.COLUMN_BALANCE };
    private String[] transactionColumns = { DataHelper.COLUMN_ID, DataHelper.COLUMN_ACCOUNT,
            DataHelper.COLUMN_AMOUNT, DataHelper.COLUMN_TYPE, DataHelper.COLUMN_CATEGORY,
            DataHelper.COLUMN_DATE };

    public DataHandler(Context context) {
        helper = new DataHelper(context);
    }

    /**
     * Takes an Account object and stores it in the database.
     * @param a The Account object to be saved.
     */
    public void saveAccount(Account a) {
        try {
            db = helper.getWritableDatabase();
            String balance = a.getBalance().toPlainString();
            ContentValues values = new ContentValues();
            values.put(DataHelper.COLUMN_ACCOUNT, a.getName());
            values.put(DataHelper.COLUMN_BALANCE, balance);
    
            long id = db.insert(DataHelper.TABLE_ACCOUNTS, null, values);
            a.setId(id);
        } finally {
            db.close();
        }
    }

    /**
     * Takes a Transaction object and stores it in the database.
     * @param t The Transaction object to be saved.
     */
    public void saveTransaction(Transaction t) {
        try {
            db = helper.getWritableDatabase();
            String amount = t.getAmount().toPlainString();
            ContentValues values = new ContentValues();
            values.put(DataHelper.COLUMN_ACCOUNT, t.getAccount());
            values.put(DataHelper.COLUMN_AMOUNT, amount);
            values.put(DataHelper.COLUMN_TYPE, t.getType());
            values.put(DataHelper.COLUMN_CATEGORY, t.getCategory());
            values.put(DataHelper.COLUMN_DATE, t.getDate());
    
            long id = db.insert(DataHelper.TABLE_TRANSACTIONS, null, values);
            t.setId(id);
            
            updateAccount(t);
        } finally {
            db.close();
        }
    }

    /**
     * Takes an Account object and removes it and all its transactions from the
     * database.
     * 
     * @param a The Account object to be deleted.
     */
    public void deleteAccount(Account a) {
        db = helper.getWritableDatabase();
        String name = a.getName();

        // Removes account from account table.
        db.delete(DataHelper.TABLE_ACCOUNTS, DataHelper.COLUMN_ACCOUNT + "='" + name + "'", null);

        // Removes account from transactions table.
        db.delete(DataHelper.TABLE_TRANSACTIONS, DataHelper.COLUMN_ACCOUNT + "='" + name + "'",
                null);

        db.close();
    }
    
    

    /**
     * Takes a Transaction object and removes it from the database.
     * @param t The transaction object to be deleted.
     */
    public void deleteTransaction(Transaction t) {
        db = helper.getWritableDatabase();
        long id = t.getId();
        db.delete(DataHelper.TABLE_TRANSACTIONS, DataHelper.COLUMN_ID + "=" + id, null);
        
        if(t.getType() == 0) {
            t.setType(1);
        } else if(t.getType() == 1) {
            t.setType(0);
        }
        updateAccount(t);
        db.close();
    }

    /**
     * Gets all accounts.
     * 
     * @return List<Account>
     */
    public ArrayList<Account> getAllAccounts() {
        ArrayList<Account> accounts = new ArrayList<Account>();
        try {
            db = helper.getWritableDatabase();
            Cursor cursor = db.query(DataHelper.TABLE_ACCOUNTS, accountColumns, null, null, null,
                    null, null);
            cursor.moveToFirst();
            while (!cursor.isAfterLast()) {
                Account a = cursorToAccount(cursor);
                accounts.add(a);
                cursor.moveToNext();
            }
        } finally {
            db.close();
        }
        return accounts;
    }

    /**
     * Gets all the transactions for the specified account name.
     * 
     * @return List<Transaction>
     */
    public ArrayList<Transaction> getAllTransactions(String account) {
        ArrayList<Transaction> transactions = new ArrayList<Transaction>();
        try {
            db = helper.getWritableDatabase();
            Cursor cursor = db.query(DataHelper.TABLE_TRANSACTIONS, transactionColumns,
                    DataHelper.COLUMN_ACCOUNT + "=?", new String[] { account }, null, null,
                    DataHelper.COLUMN_ID + " DESC");
            cursor.moveToFirst();
            while (!cursor.isAfterLast()) {
                Transaction t = cursorToTransaction(cursor);
                t.setAccount(account);
                transactions.add(t);
                cursor.moveToNext();
            }
            cursor.close();
        } finally {
            db.close();
        }
        return transactions;
    }

    /**
     * Checks whether accounts database is empty or not.
     */
    public boolean isAccountsEmpty() {
        try {
            db = helper.getWritableDatabase();
            Cursor cursor = db.query(DataHelper.TABLE_ACCOUNTS, accountColumns, 
                    null, null, null, null,null);
            return !cursor.moveToFirst();
        } finally {
            db.close();
        }
    }

    /**
     * Checks whether transactions database for given account name is empty or
     * not.
     * 
     * @param account Name of account to check.
     */
    public boolean isTransactionsEmpty(String account) {
        try { 
            db = helper.getWritableDatabase();
            Cursor cursor;
            cursor = db.query(DataHelper.TABLE_TRANSACTIONS, transactionColumns, null, null, null,
                    null, null);
            if (cursor.moveToFirst()) {
                cursor = db.query(DataHelper.TABLE_TRANSACTIONS, transactionColumns,
                        DataHelper.COLUMN_ACCOUNT + "=?", new String[] { account }, null, null, null);
                
                return !cursor.moveToFirst();
            } else {
                return true;
            }
        } finally {
            db.close();
        }
    }

    private void updateAccount(Transaction t) {
        db = helper.getWritableDatabase();
        ContentValues values = new ContentValues();
        Cursor cursor = db.query(DataHelper.TABLE_ACCOUNTS, new String[] { DataHelper.COLUMN_BALANCE }, 
                DataHelper.COLUMN_ACCOUNT + "=?", new String[] { t.getAccount() }, null, null, null);
        cursor.moveToFirst();
        
        BigDecimal currentBalance = new BigDecimal(cursor.getString(0));
        BigDecimal newBalance;
        if (t.getType() == 0) {
            newBalance = currentBalance.subtract(t.getAmount());
        } else {
            newBalance = currentBalance.add(t.getAmount());
        }
        
        String balance = newBalance.toPlainString();
        values.put(DataHelper.COLUMN_BALANCE, balance);
        db.update(DataHelper.TABLE_ACCOUNTS, values, DataHelper.COLUMN_ACCOUNT + "=?",
                new String[] { t.getAccount() });
        db.close();
    }
    
    public void changeAccountName(Account a, String newName) {
    	db = helper.getWritableDatabase();
    	ContentValues values = new ContentValues();    
    	values.put(DataHelper.COLUMN_ACCOUNT, newName);
    	db.update(DataHelper.TABLE_ACCOUNTS, values, DataHelper.COLUMN_ID + "=?", new String[]{String.valueOf(a.getId())});
    	db.update(DataHelper.TABLE_TRANSACTIONS, values, DataHelper.COLUMN_ACCOUNT + "=?", new String[]{a.getName()});
    	db.close();
    }

    private Account cursorToAccount(Cursor cursor) {
        Account a = new Account();
        a.setId(cursor.getInt(0));
        a.setName(cursor.getString(1));
        a.setBalance(new BigDecimal(cursor.getString(2)));
        return a;
    }

    private Transaction cursorToTransaction(Cursor cursor) {
        Transaction t = new Transaction();
        t.setId(cursor.getLong(0));
        t.setAmount(new BigDecimal(cursor.getDouble(2)));
        t.setType(cursor.getInt(3));
        t.setCategory(cursor.getString(4));
        t.setDate(cursor.getString(5));
        return t;
    }

    /**
     * Creates and updates the database.
     */
    private class DataHelper extends SQLiteOpenHelper {
        private static final int DATABASE_VERSION = 1;
        private static final String DATABASE_NAME = "transactions.db";
        private static final String TABLE_ACCOUNTS = "accounts";
        private static final String TABLE_TRANSACTIONS = "transactions";

        // Columns
        private static final String COLUMN_ID = "_id";
        private static final String COLUMN_ACCOUNT = "account";
        private static final String COLUMN_AMOUNT = "amount";
        private static final String COLUMN_BALANCE = "balance";
        private static final String COLUMN_TYPE = "type";
        private static final String COLUMN_CATEGORY = "category";
        private static final String COLUMN_DATE = "date";

        // Account table creation statement
        private static final String ACCOUNTS_CREATE = " create table " + TABLE_ACCOUNTS + "("
                + COLUMN_ID + " integer primary key autoincrement, " + COLUMN_ACCOUNT + " text, "
                + COLUMN_BALANCE + " text);";

        // Transaction table creation statement
        private static final String TRANSACTIONS_CREATE = " create table " + TABLE_TRANSACTIONS
                + "(" + COLUMN_ID + " integer primary key autoincrement, " + COLUMN_ACCOUNT
                + " text, " + COLUMN_AMOUNT + " text, " + COLUMN_TYPE + " integer, "
                + COLUMN_CATEGORY + " text, " + COLUMN_DATE + " text);";

        public DataHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(ACCOUNTS_CREATE);
            db.execSQL(TRANSACTIONS_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACCOUNTS);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_TRANSACTIONS);
            onCreate(db);
        }
    }
}